package com.petshop.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.common.constant.BalzLogger;
import com.common.constant.ProjectConstant;
import com.common.jdbc.JDBCConnection;
import com.petshop.model.User;
import com.petshop.model.UserProfile;
import com.petshop.model.UserRole;


public class RegisterDaoImpl extends JDBCConnection implements RegisterDao{
	
	BalzLogger logger = new BalzLogger();
	
	private static final StringBuilder SQL_SELECT_BALZ_USER= new StringBuilder().append(" SELECT * FROM balz_user ");
	private static final StringBuilder SQL_SELECT_PROFILE= new StringBuilder().append(" SELECT * FROM ps_profile ");
	
	@Override
	public Boolean checkUsernameExist(String username) {
		
		String sql = SQL_SELECT_BALZ_USER.toString()+"where username ='"+username+"' ";
	
		ResultSet rs = queryBySql(sql);
		List<User> userProfiles = new ArrayList<User>();
		
		Boolean isExist = true;
		
		try {
			while (rs.next()) {
				userProfiles.add(userMapRow(rs)); // Put the Data Model to UserProfile list
			}
			rs.close();
		} catch (SQLException e) {
	    	logger.debug("SQLException: " + e.getMessage());
	    }
		
		if (logger.isDebugEnabled()) {
			logger.debug("sql: " + sql);
			logger.debug("result.size(): " + userProfiles.size());
			logger.debug("result>> " + userProfiles);
		}
		
		if(userProfiles.size() == 0){
			isExist = false;
		}
		return isExist;
	}
	
	@Override
	public Boolean checkEmailExist(String email) {
		
		String sql = SQL_SELECT_PROFILE.toString()+"where email ='"+email+"' ";
	
		ResultSet rs = queryBySql(sql);
		List<UserProfile> userProfiles = new ArrayList<UserProfile>();
		
		Boolean isExist = true;
		
		try {
			while (rs.next()) {
				userProfiles.add(profileMapRow(rs)); // Put the Data Model to UserProfile list
			}
			rs.close();
		} catch (SQLException e) {
	    	logger.debug("SQLException: " + e.getMessage());
	    }
		
		if (logger.isDebugEnabled()) {
			logger.debug("sql: " + sql);
			logger.debug("result.size(): " + userProfiles.size());
			logger.debug("result>> " + userProfiles);
		}
		
		if(userProfiles.size() == 0){
			isExist = false;
		}
		return isExist;
	}
	
	
	@Override
	public int registUserNamePassword(User user) {
		
		StringBuilder sql = new StringBuilder();
		sql.append(" INSERT INTO balz_user   ");
		sql.append(" (           ");
		sql.append(" USERNAME,           ");
		sql.append(" PASSWORD,           ");
		sql.append(" FORCE_PWD_CHNG_FLAG,");
		sql.append(" LAST_LOGIN,         ");
		sql.append(" STATUS,             ");
		sql.append(" CREATED_DATE,       ");
		sql.append(" UPDATED_DATE )   ");
		sql.append(" VALUES  ");
		sql.append(" (");
		sql.append(" '"+user.getUserName()+"' " );
		sql.append(",   '"+user.getPassword()+"' ");
		sql.append(", 'N', NULL, 'Y', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP ) ");
		

		// Get UserID
		int userId = 0;
		
		try {
			getStatement().executeUpdate(sql.toString());
			getStatement().close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		userId = getUserIdByUserName(user.getUserName());
		
		if (logger.isDebugEnabled()) {
			logger.debug("sql: " + sql);
			logger.debug("userId >> " + userId);
		}
		
		return userId;
	}
	
	private int getUserIdByUserName(String username) {
		
		String sql = SQL_SELECT_BALZ_USER.toString()+"where USERNAME ='"+username.trim()+"' ";
		
		ResultSet rs = queryBySql(sql);
		List<User> userProfiles = new ArrayList<User>();
		
		int userId = 0;
		
		try {
			while (rs.next()) {
				userProfiles.add(userMapRow(rs));
			}
			rs.close();
		} catch (SQLException e) {
	    	logger.debug("SQLException: " + e.getMessage());
	    }
		
		if(userProfiles.size() != 0){
			userId = Integer.parseInt(userProfiles.get(0).getUserId());
		}
		
		if (logger.isDebugEnabled()) {
			logger.debug("sql: " + sql);
			logger.debug("result.size(): " + userProfiles.size());
			logger.debug("result>> " + userProfiles);
			logger.debug("userId >> " + userId);
		}
		
		return userId;
	}
	

	@Override
	public void registProfile(User user) {
		
		logger.debug("USER >> : "+user);
		
		StringBuilder sql = new StringBuilder();
		sql.append(" INSERT INTO ps_profile ");
		sql.append(" (  ");
		sql.append(" USER_ID, ");
		sql.append(" ADDRESS, ");
		sql.append(" TELEPHONE,");
		sql.append(" EMAIL, ");
		sql.append(" STATUS )");
		sql.append(" VALUES(");
		sql.append("   '"+user.getUserId()+"' ");
		sql.append(" ,   '"+user.getUserProfile().getAddress()+"' ");
		sql.append(" ,   '"+user.getUserProfile().getTel()+"' ");
		sql.append(" ,   '"+user.getUserProfile().getEmail()+"' ");
		sql.append(" ,'Y')");
		
		try {
			getStatement().executeUpdate(sql.toString());
			getStatement().close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		if (logger.isDebugEnabled()) {
			logger.debug("sql: " + sql);
		}
	}
	
	
	@Override
	public void registRole(User user) {
		
		logger.debug("USER >> : "+user);
		
		StringBuilder sql = new StringBuilder();
		sql.append(" INSERT INTO balz_user_map_role   ");
		sql.append(" (USER_ID,          ");
		sql.append(" ROLE_ID,           ");
		sql.append(" STATUS,            ");
		sql.append(" CREATED_DATE,      ");
		sql.append(" UPDATED_DATE )     ");
		sql.append(" VALUES             ");
		sql.append(" (  '"+ user.getUserId() +"', ");
		sql.append(" '2', ");
		sql.append(" 'Y', ");
		sql.append(" CURRENT_TIMESTAMP,    ");
		sql.append(" CURRENT_TIMESTAMP )    ");
		
		try {
			getStatement().executeUpdate(sql.toString());
			getStatement().close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		if (logger.isDebugEnabled()) {
			logger.debug("sql: " + sql);
		}
	}
	
	
	
	public User userMapRow(ResultSet rs) throws SQLException {
		
		User model = new User();
		
		model.setUserId(rs.getString("USER_ID"));
		model.setUserName(rs.getString("USERNAME"));
		model.setPassword(rs.getString("PASSWORD"));
		model.setForcePassChangeFlag(rs.getString("FORCE_PWD_CHNG_FLAG"));
		model.setLastLogin(rs.getTimestamp("LAST_LOGIN"));
		model.setStatus(rs.getString("STATUS"));
		model.setCreatedBy(rs.getString("CREATED_BY"));
		model.setCreatedDate(rs.getTimestamp("CREATED_DATE"));
		model.setUpdatedBy(rs.getString("UPDATED_BY"));
		model.setUpdatedDate(rs.getTimestamp("UPDATED_DATE"));
		return model;
	}
	
	public UserProfile profileMapRow(ResultSet rs) throws SQLException {
		
		UserProfile model = new UserProfile();
		
		model.setProfileId(rs.getString("PROFILE_ID"));
		model.setUserId(rs.getString("USER_ID"));
		model.setFirstName(rs.getString("FIRST_NAME"));
		model.setLastName(rs.getString("LAST_NAME"));
		model.setEmail(rs.getString("EMAIL"));
		model.setAddress(rs.getString("ADDRESS"));
		model.setTel(rs.getString("TELEPHONE"));
		model.setStatus(rs.getString("STATUS"));
		
		return model;
	}

	
	
}
